﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Web.Configuration;
using FlashCards.Models;
using FlashCards.WebApplication.Maintenance;

namespace FlashCards.WebApplication.DAL
{
    public static class DataBaseConnector
    {
        #region PRIVATE FIELDS

        static string _connString = WebConfigurationManager.ConnectionStrings["FishCardsDbConnectionString"].ConnectionString;

        #endregion

        #region METHODS

        #region GENERAL METHODS

        public static SqlDataReader ExecuteReaderProcedure(string procName, CommandType type)
        {
            SqlConnection conn = new SqlConnection(_connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = type;
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static SqlDataReader ExecuteReaderProcedure(string procName, CommandType type, SqlParameter parameter)
        {
            SqlConnection conn = new SqlConnection(_connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn) { CommandType = type };
            cmd.Parameters.Add(parameter);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static SqlDataReader ExecuteReaderProcedure(string procName, CommandType type, SqlParameter [] parameters)
        {
            SqlConnection conn = new SqlConnection(_connString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(procName, conn) { CommandType = type };
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection); 
        }

        public static int ExecuteNonQueryProcedure(SqlCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                conn.Open();
                cmd.Connection = conn;
                return cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region PROCEDURE-SPECIFIC METHODS

        public static User GetUserByName(string name)
        {
            User user = null;
            SqlParameter param = new SqlParameter("@Name", SqlDbType.VarChar, 25) { Value = name };
            SqlDataReader reader = ExecuteReaderProcedure("SelectUserByName", CommandType.StoredProcedure, param);
            using (reader)
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    string login = reader["Login"] as string;
                    string password = reader["Password"] as string;
                    user = new User(login, password);
                    //pobieranie listy znajomych
                    SqlParameter friendParam = new SqlParameter("@Name", SqlDbType.VarChar, 25) { Value = name };
                    using (SqlDataReader friendsReader = ExecuteReaderProcedure("SelectFriendsByName", CommandType.StoredProcedure, friendParam))
                    {
                        if (friendsReader.HasRows)
                        {
                            while (friendsReader.Read())
                            {
                                user.Friends.Add(friendsReader["Sender"] as string);
                            }
                        }
                    }

                }
            }
            return user;
        }

        public static void AddNewUser(User user)
        {
            try
            {
                if (user != null)
                {
                    using (SqlCommand cmd = new SqlCommand("InsertUser") { CommandType = CommandType.StoredProcedure })
                    {
                        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 25).Value = user.Login;
                        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 15).Value = user.Password;
                        int rowsAffected = ExecuteNonQueryProcedure(cmd);
                        Logger.Log("DataBaseConnector / AddNewUser()", string.Format("Added new user to the database. Login {0}, Pswd: {1}. Rows affected: {2}", new object[] { user.Login, user.Password, rowsAffected }));
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Log("DataBaseConnector / AddNewUser", ex.Message);
            }
        }

        #endregion

        #endregion
    }
}